const connection = require("../app/database");

class accommodationService {
  // 发布
  async releaseAccommodationService(parameter) {
    const {
      uid,
      name,
      facilities,
      other_facilities,
      charge,
      people_ceiling,
      bed_ceiling,
      house_type,
      rental_type,
      house_pattern,
      description,
      surrounding_area,
      location,
      requirement,
      images,
      view_counts,
      longitude,
      latitude,
    } = parameter;
    const statement = `
    INSERT INTO accommodation (
      uid,
      name,
      facilities,
      other_facilities,
      charge,
      people_ceiling,
      bed_ceiling,
      house_type,
      rental_type,
      house_pattern,
      description,
      surrounding_area,
      location,
      requirement,
      images,
      view_counts,
      longitude,
      latitude
  ) VALUES (
      ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?
  );
    `;
    try {
      const [result] = await connection.execute(statement, [
        uid,
        name,
        facilities,
        other_facilities,
        charge,
        people_ceiling,
        bed_ceiling,
        house_type,
        rental_type,
        house_pattern,
        description,
        surrounding_area,
        location,
        requirement,
        images,
        view_counts,
        longitude,
        latitude,
      ]);
      return result;
    } catch (error) {
      console.log(error);
    }
  }

  // 获取
  async getAccommodationService(parameter) {
    const field = {
      lists: [],
      total: 0,
    };

    try {
      const statement1 = `SELECT COUNT(*) as total FROM accommodation a WHERE
    a.is_delete = 0 AND a.audit_state = 2`;
      const [totalResult] = await connection.execute(statement1);
      field.total = totalResult[0].total;

      if (!totalResult[0].total) {
        return field;
      }

      const { offset, limit, longitude, latitude } = parameter;
      const statement2 = `
    SELECT
      a.id AS accommodation_id,
      JSON_OBJECT('id',u.id,'name',u.name,'createTime',u.createAt,'avatar',u.avatar,'account',u.account) AS user,
      ST_DISTANCE_SPHERE(POINT(a.longitude, a.latitude), POINT(?, ?)) AS distance,
      a.name,
      a.facilities,
      a.other_facilities,
      a.charge,
      a.people_ceiling,
      a.bed_ceiling,
      a.house_type,
      a.rental_type,
      a.house_pattern,
      a.description,
      a.surrounding_area,
      a.location,
      a.requirement,
      a.images,
      a.view_counts
    FROM
        accommodation a
    LEFT JOIN user u ON u.id = a.uid 
    WHERE
        a.is_delete = 0 AND  a.audit_state = 2
    ORDER BY
      distance ASC  -- 先按距离升序排序
    LIMIT
        ?  
    OFFSET
        ?;
    `;
      const [result] = await connection.execute(statement2, [
        longitude,
        latitude,
        limit,
        offset,
      ]);
      field.lists = result;

      return field;
    } catch (error) {
      console.log(error);
    }
  }

  // 熱度获取
  async getAccommodationByHotService(parameter) {
    const field = {
      lists: [],
      total: 0,
    };

    try {
      const statement1 = `SELECT COUNT(*) as total FROM accommodation a WHERE
    a.is_delete = 0 AND a.audit_state = 2`;
      const [totalResult] = await connection.execute(statement1);
      field.total = totalResult[0].total;

      if (!totalResult[0].total) {
        return field;
      }

      const { offset, limit } = parameter;
      const statement2 = `
    SELECT
      a.id AS accommodation_id,
      JSON_OBJECT('id',u.id,'name',u.name,'createTime',u.createAt,'avatar',u.avatar,'account',u.account) AS user,
      a.name,
      a.facilities,
      a.other_facilities,
      a.charge,
      a.people_ceiling,
      a.bed_ceiling,
      a.house_type,
      a.rental_type,
      a.house_pattern,
      a.description,
      a.surrounding_area,
      a.location,
      a.requirement,
      a.images,
      a.view_counts
    FROM
        accommodation a
    LEFT JOIN user u ON u.id = a.uid 
    WHERE
        a.is_delete = 0 AND  a.audit_state = 2
    ORDER BY
        a.view_counts DESC  -- 先按距离升序排序
    LIMIT
        ?  
    OFFSET
        ?;
    `;
      const [result] = await connection.execute(statement2, [limit, offset]);
      field.lists = result;

      return field;
    } catch (error) {
      console.log(error);
    }
  }

  // 模糊查询
  async likeAccommodationService(keyWord, limit, offset) {
    const field = {
      lists: [],
      total: 0,
    };

    try {
      const statement1 = `SELECT COUNT(*) as total FROM accommodation a WHERE
      a.is_delete = 0 AND a.audit_state = 2
      AND (
          a.name LIKE ? OR
          a.facilities LIKE ? OR
          a.other_facilities LIKE ? OR
          a.charge LIKE ? OR
          a.people_ceiling LIKE ? OR
          a.bed_ceiling LIKE ? OR
          a.house_type LIKE ? OR
          a.rental_type LIKE ? OR
          a.house_pattern LIKE ? OR
          a.surrounding_area LIKE ? OR
          a.location LIKE ? OR
          a.requirement LIKE ? 
      )`;
      const params2 = Array(12).fill(`%${keyWord}%`);
      const [totalResult] = await connection.execute(statement1, params2);
      field.total = totalResult[0].total;

      if (!totalResult[0].total) {
        return field;
      }

      const statement2 = `
      SELECT
        a.id AS accommodation_id,
        JSON_OBJECT('id', u.id, 'name', u.name, 'createTime', u.createAt, 'avatar', u.avatar, 'account', u.account) AS user,
        a.name,
        a.facilities,
        a.other_facilities,
        a.charge,
        a.people_ceiling,
        a.bed_ceiling,
        a.house_type,
        a.rental_type,
        a.house_pattern,
        a.description,
        a.surrounding_area,
        a.location,
        a.requirement,
        a.images,
        a.view_counts
    FROM
        accommodation a
    LEFT JOIN user u ON u.id = a.uid
    WHERE
        a.is_delete = 0 AND a.audit_state = 2
        AND (
            a.name LIKE ? OR
            a.facilities LIKE ? OR
            a.other_facilities LIKE ? OR
            a.charge LIKE ? OR
            a.people_ceiling LIKE ? OR
            a.bed_ceiling LIKE ? OR
            a.house_type LIKE ? OR
            a.rental_type LIKE ? OR
            a.house_pattern LIKE ? OR
            a.surrounding_area LIKE ? OR
            a.location LIKE ? OR
            a.requirement LIKE ? 
        )
    ORDER BY
        a.view_counts DESC
    LIMIT
        ? OFFSET ?;
      `;
      const params1 = Array(12).fill(`%${keyWord}%`);
      params1.push(limit);
      params1.push(offset);
      const [result] = await connection.execute(statement2, params1);
      field.lists = result;

      return field;
    } catch (error) {
      console.log(error);
    }
  }

  // 收藏
  async saveAccommodationService(parameter, method) {
    let statement = "";

    if (method === "post") {
      statement = `
      INSERT INTO accommodation_save (uid,accommodation_id,accommodation_uid) VALUES 
  (?,?,?)
      `;
    }
    if (method === "delete") {
      statement = `
      DELETE FROM accommodation_save WHERE uid = ? AND accommodation_id = ? AND accommodation_uid = ?;
      `;
    }
    if (method === "get") {
      statement = `
      SELECT COUNT(*) AS isSave FROM accommodation_save WHERE uid = ? AND accommodation_id = ? AND accommodation_uid = ?;
      `;
    }
    const { uid, accommodation_id, accommodation_uid } = parameter;

    try {
      const [result] = await connection.execute(statement, [
        uid,
        accommodation_id,
        accommodation_uid,
      ]);
      return result;
    } catch (error) {
      console.log(error);
    }
  }

  // 获取
  async getSaveAccommodationService(parameter) {
    const { uid, offset, limit } = parameter;
    const field = {
      lists: [],
      total: 0,
    };

    try {
      const statement1 = `SELECT COUNT(*) as total FROM accommodation_save acs WHERE acs.uid = ?`;
      const [totalResult] = await connection.execute(statement1, [uid]);
      field.total = totalResult[0].total;

      if (!totalResult[0].total) {
        return field;
      }

      const statement2 = `
      SELECT
          a.id AS accommodation_id,
          JSON_OBJECT('id',u.id,'name',u.name,'createTime',u.createAt,'avatar',u.avatar,'account',u.account) AS user,
          a.name,
          a.facilities,
          a.other_facilities,
          a.charge,
          a.people_ceiling,
          a.bed_ceiling,
          a.house_type,
          a.rental_type,
          a.house_pattern,
          a.description,
          a.surrounding_area,
          a.location,
          a.requirement,
          a.images,
          a.view_counts
      FROM
          accommodation a
      LEFT JOIN user u ON u.id = a.uid 
      LEFT JOIN accommodation_save acs ON acs.accommodation_id = a.id
      WHERE
          a.is_delete = 0 AND  a.audit_state = 2 AND acs.uid = ?
      ORDER BY
           a.view_counts DESC
      LIMIT
          ?  
      OFFSET
          ?;
        `;
      const [result] = await connection.execute(statement2, [
        uid,
        limit,
        offset,
      ]);
      field.lists = result;

      return field;
    } catch (error) {
      console.log(error);
    }
  }

  // 下单
  async reservationService(parameter, method) {
    let statement = "";
    const { uid, accommodation_id, start_time, end_time } = parameter;
    if (method === "post") {
      statement = `
      INSERT INTO reservation 
      (uid,accommodation_id,start_time, end_time)
      VALUES (?,?,?,?)
      `;
    }
    if (method === "delete") {
      statement = `
      DELETE FROM reservation WHERE uid = ? AND accommodation_id = ? AND start_time = ? AND end_time = ?
      `;
    }

    try {
      const [result] = await connection.execute(statement, [
        uid,
        accommodation_id,
        start_time,
        end_time,
      ]);
      return result;
    } catch (error) {
      console.log(error);
    }
  }

  // 获取下单
  async getReservationService(parameter) {
    const { uid, offset, limit } = parameter;
    const field = {
      lists: [],
      total: 0,
    };

    try {
      const statement1 = `SELECT COUNT(*) as total FROM reservation r WHERE r.uid = ?`;
      const [totalResult] = await connection.execute(statement1, [uid]);
      field.total = totalResult[0].total;

      if (!totalResult[0].total) {
        return field;
      }

      const statement2 = `
      SELECT
          a.id AS accommodation_id,
          JSON_OBJECT('id',u.id,'name',u.name,'createTime',u.createAt,'avatar',u.avatar,'account',u.account) AS user,
          a.name,
          a.facilities,
          a.other_facilities,
          a.charge,
          a.people_ceiling,
          a.bed_ceiling,
          a.house_type,
          a.rental_type,
          a.house_pattern,
          a.description,
          a.surrounding_area,
          a.location,
          a.requirement,
          a.images,
          a.view_counts,
      		r.start_time,
      		r.end_time
      FROM
          accommodation a
      LEFT JOIN user u ON u.id = a.uid 
      LEFT JOIN reservation r ON r.accommodation_id = a.id
      WHERE
          a.is_delete = 0 AND  a.audit_state = 2 AND  r.audit_id = 2  AND r.uid = ?
      ORDER BY
           a.view_counts DESC
      LIMIT
          ?  -- 每页显示的记录数
      OFFSET
          ?;
        `;
      const [result] = await connection.execute(statement2, [
        uid,
        limit,
        offset,
      ]);
      field.lists = result;

      return field;
    } catch (error) {
      console.log(error);
    }
  }
}

module.exports = new accommodationService();
